Public Class AudiometryResultHistory
    Protected _year As String
    Protected _police As New Police()
    Protected _organization As New Organization()
    Protected _dB_left_250 As Integer
    Protected _dB_left_500 As Integer
    Protected _dB_left_1000 As Integer
    Protected _dB_left_2000 As Integer
    Protected _dB_left_4000 As Integer
    Protected _dB_left_8000 As Integer
    Protected _dB_right_250 As Integer
    Protected _dB_right_500 As Integer
    Protected _dB_right_1000 As Integer
    Protected _dB_right_2000 As Integer
    Protected _dB_right_4000 As Integer
    Protected _dB_right_8000 As Integer
    Protected _left_audiometry_result_status As Boolean
    Protected _right_audiometry_result_status As Boolean
    Protected _police_fullname As String
    Protected _police_age As Short
    Protected _Data As ArrayList
    Protected _DataCurrent As Integer
    Protected _audiometry_abnormal_result_history As New AudiometryAbnormalResultHistory

    Public Property year() As String
        Get
            Return _year
        End Get
        Set(ByVal value As String)
            _year = value
        End Set
    End Property

    Public Property police() As Police
        Get
            Return _police
        End Get
        Set(ByVal value As Police)
            _police = value
        End Set
    End Property

    Public Property organization() As Organization
        Get
            Return _organization
        End Get
        Set(ByVal value As Organization)
            _organization = value
        End Set
    End Property

    Public Property dB_left_250() As Integer
        Get
            Return _dB_left_250
        End Get
        Set(ByVal value As Integer)
            _dB_left_250 = value
        End Set
    End Property

    Public Property dB_left_500() As Integer
        Get
            Return _dB_left_500
        End Get
        Set(ByVal value As Integer)
            _dB_left_500 = value
        End Set
    End Property

    Public Property dB_left_1000() As Integer
        Get
            Return _dB_left_1000
        End Get
        Set(ByVal value As Integer)
            _dB_left_1000 = value
        End Set
    End Property

    Public Property dB_left_2000() As Integer
        Get
            Return _dB_left_2000
        End Get
        Set(ByVal value As Integer)
            _dB_left_2000 = value
        End Set
    End Property

    Public Property dB_left_4000() As Integer
        Get
            Return _dB_left_4000
        End Get
        Set(ByVal value As Integer)
            _dB_left_4000 = value
        End Set
    End Property

    Public Property dB_left_8000() As Integer
        Get
            Return _dB_left_8000
        End Get
        Set(ByVal value As Integer)
            _dB_left_8000 = value
        End Set
    End Property

    Public Property dB_right_250() As Integer
        Get
            Return _dB_right_250
        End Get
        Set(ByVal value As Integer)
            _dB_right_250 = value
        End Set
    End Property

    Public Property dB_right_500() As Integer
        Get
            Return _dB_right_500
        End Get
        Set(ByVal value As Integer)
            _dB_right_500 = value
        End Set
    End Property

    Public Property dB_right_1000() As Integer
        Get
            Return _dB_right_1000
        End Get
        Set(ByVal value As Integer)
            _dB_right_1000 = value
        End Set
    End Property

    Public Property dB_right_2000() As Integer
        Get
            Return _dB_right_2000
        End Get
        Set(ByVal value As Integer)
            _dB_right_2000 = value
        End Set
    End Property

    Public Property dB_right_4000() As Integer
        Get
            Return _dB_right_4000
        End Get
        Set(ByVal value As Integer)
            _dB_right_4000 = value
        End Set
    End Property

    Public Property dB_right_8000() As Integer
        Get
            Return _dB_right_8000
        End Get
        Set(ByVal value As Integer)
            _dB_right_8000 = value
        End Set
    End Property

    Public Property right_audiometry_result_status() As Boolean
        Get
            Return _right_audiometry_result_status
        End Get
        Set(ByVal value As Boolean)
            _right_audiometry_result_status = value
        End Set
    End Property

    Public Property left_audiometry_result_status() As Boolean
        Get
            Return _left_audiometry_result_status
        End Get
        Set(ByVal value As Boolean)
            _left_audiometry_result_status = value
        End Set
    End Property

    Public Property police_fullname() As String
        Get
            Return _police_fullname
        End Get
        Set(ByVal value As String)
            _police_fullname = value
        End Set
    End Property

    Public Property police_age() As Short
        Get
            Return _police_age
        End Get
        Set(ByVal value As Short)
            _police_age = value
        End Set
    End Property

    Public Function getPoliceAudiometry(ByVal inOrganizationID As String, ByVal inYear As String) As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceAudiometryResultHistory"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceAudiometry")

        Return objDs
    End Function

    Protected Function getsPoliceAudiometry(ByVal inOrganizationID As String, ByVal inYear As String) As ArrayList
        Dim outResult As New ArrayList()
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceAudiometryResultHistory"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceAudiometry")

        For Each objDr As System.Data.DataRow In objDs.Tables("PoliceAudiometry").Rows
            Dim obj As AudiometryResultHistory = New AudiometryResultHistory
            obj.police.citizen_id = objDr.Item("citizen_id")
            obj.police_fullname = objDr.Item("full_name")
            obj.police_age = Utilities.ClearDBNullToMinValue(objDr.Item("age"), TypeCode.Int32)
            obj.dB_left_250 = Utilities.ClearDBNullToMinValue(objDr.Item("l_250"), TypeCode.Int32)
            obj.dB_left_500 = Utilities.ClearDBNullToMinValue(objDr.Item("l_500"), TypeCode.Int32)
            obj.dB_left_1000 = Utilities.ClearDBNullToMinValue(objDr.Item("l_1000"), TypeCode.Int32)
            obj.dB_left_2000 = Utilities.ClearDBNullToMinValue(objDr.Item("l_2000"), TypeCode.Int32)
            obj.dB_left_4000 = Utilities.ClearDBNullToMinValue(objDr.Item("l_4000"), TypeCode.Int32)
            obj.dB_left_8000 = Utilities.ClearDBNullToMinValue(objDr.Item("l_8000"), TypeCode.Int32)
            obj.dB_right_250 = Utilities.ClearDBNullToMinValue(objDr.Item("r_250"), TypeCode.Int32)
            obj.dB_right_500 = Utilities.ClearDBNullToMinValue(objDr.Item("r_500"), TypeCode.Int32)
            obj.dB_right_1000 = Utilities.ClearDBNullToMinValue(objDr.Item("r_1000"), TypeCode.Int32)
            obj.dB_right_2000 = Utilities.ClearDBNullToMinValue(objDr.Item("r_2000"), TypeCode.Int32)
            obj.dB_right_4000 = Utilities.ClearDBNullToMinValue(objDr.Item("r_4000"), TypeCode.Int32)
            obj.dB_right_8000 = Utilities.ClearDBNullToMinValue(objDr.Item("r_8000"), TypeCode.Int32)
            outResult.Add(obj)
        Next

        Return outResult
    End Function

    Public Function insertPoliceAudiometryResultHistory _
    (ByVal inPoliceAudiometryResultHistory As AudiometryResultHistory) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_PoliceAudiometryResultHistory"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", _
                Utilities.SendToDatabase(Utilities.ConvertCitizenID(inPoliceAudiometryResultHistory.police.citizen_id)))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.police.organization.organization_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationNameTha", _
                 Utilities.SendToDatabase(inPoliceAudiometryResultHistory.police.organization.organization_name_tha))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.year))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBLeft250", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_left_250))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBLeft500", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_left_500))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBLeft1000", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_left_1000))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBLeft2000", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_left_2000))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBLeft4000", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_left_4000))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBLeft8000", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_left_8000))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBRight250", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_right_250))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBRight500", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_right_500))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBRight1000", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_right_1000))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBRight2000", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_right_2000))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBRight4000", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_right_4000))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("indBRight8000", _
                Utilities.SendToDatabase(inPoliceAudiometryResultHistory.dB_right_8000))
            objCmd.Parameters.Add(objParam)

            _audiometry_abnormal_result_history.deletePoliceAudiometryAbnormalResultHistory _
                (inPoliceAudiometryResultHistory)

            If Not (inPoliceAudiometryResultHistory.dB_left_500 = Integer.MinValue OrElse inPoliceAudiometryResultHistory.dB_left_1000 = Integer.MinValue OrElse inPoliceAudiometryResultHistory.dB_left_2000 = Integer.MinValue) Then
                inPoliceAudiometryResultHistory.left_audiometry_result_status = _
               _audiometry_abnormal_result_history.insertPoliceAudiometryAbnormalStatusHistory _
               (inPoliceAudiometryResultHistory, 1)
                objParam = New MySql.Data.MySqlClient.MySqlParameter("inLeftAudiometryResultStatus", _
                    Utilities.SendToDatabase(inPoliceAudiometryResultHistory.left_audiometry_result_status))
                objCmd.Parameters.Add(objParam)
            Else
                objParam = New MySql.Data.MySqlClient.MySqlParameter("inLeftAudiometryResultStatus", DBNull.Value)
                objCmd.Parameters.Add(objParam)
            End If


            If Not (inPoliceAudiometryResultHistory.dB_right_500 = Integer.MinValue OrElse inPoliceAudiometryResultHistory.dB_right_1000 = Integer.MinValue OrElse inPoliceAudiometryResultHistory.dB_right_2000 = Integer.MinValue) Then
                inPoliceAudiometryResultHistory.right_audiometry_result_status = _
                _audiometry_abnormal_result_history.insertPoliceAudiometryAbnormalStatusHistory _
                (inPoliceAudiometryResultHistory, 2)
                objParam = New MySql.Data.MySqlClient.MySqlParameter("inRightAudiometryResultStatus", _
                    Utilities.SendToDatabase(inPoliceAudiometryResultHistory.right_audiometry_result_status))
                objCmd.Parameters.Add(objParam)
            Else
                objParam = New MySql.Data.MySqlClient.MySqlParameter("inRightAudiometryResultStatus", DBNull.Value)
                objCmd.Parameters.Add(objParam)
            End If



            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Sub LoadPoliceAudiometryInOrganizationAll(ByVal inOrganizationID As String, ByVal inYear As String)
        _Data = New ArrayList
        _DataCurrent = 0
        _Data = getsPoliceAudiometry(inOrganizationID, inYear)
    End Sub

    Public Function NextRecord() As Boolean
        If ((Not _Data Is Nothing) AndAlso (((Not _Data Is Nothing) AndAlso _
            (_DataCurrent < _Data.Count And _Data.Count > 0)))) Then
            _police.citizen_id = _Data(_DataCurrent).police.citizen_id
            _police_fullname = _Data(_DataCurrent).police_fullname
            _police_age = _Data(_DataCurrent).police_age
            _dB_left_250 = _Data(_DataCurrent).dB_left_250
            _dB_left_500 = _Data(_DataCurrent).dB_left_500
            _dB_left_1000 = _Data(_DataCurrent).dB_left_1000
            _dB_left_2000 = _Data(_DataCurrent).dB_left_2000
            _dB_left_4000 = _Data(_DataCurrent).dB_left_4000
            _dB_left_8000 = _Data(_DataCurrent).dB_left_8000
            _dB_right_250 = _Data(_DataCurrent).dB_right_250
            _dB_right_500 = _Data(_DataCurrent).dB_right_500
            _dB_right_1000 = _Data(_DataCurrent).dB_right_1000
            _dB_right_2000 = _Data(_DataCurrent).dB_right_2000
            _dB_right_4000 = _Data(_DataCurrent).dB_right_4000
            _dB_right_8000 = _Data(_DataCurrent).dB_right_8000

            _DataCurrent += 1
            Return True
        End If
        Return False
    End Function

End Class
